if exists (select 1
          from sysobjects
          where  id = object_id('bd_proc_action_schedule')
          and type in ('P','PC'))
   drop procedure bd_proc_action_schedule
go


create procedure bd_proc_action_schedule (@scheduleDetailJson varchar(max),@begin_date date,@end_date date,@ip varchar(max),@operator varchar(max),@doType int = 0) as
BEGIN

	CREATE TABLE #scheduleDetail
	(
		user_serial bigint NOT NULL,
		col_name  VARCHAR(30),
		shift_id INT NOT NULL DEFAULT (0),
		shift_name VARCHAR(30) NULL,
		shift_date DATE NOT NULL,
		shift_begin INT NOT NULL DEFAULT (0),
		shift_end INT NOT NULL DEFAULT (0),
		shift_sign_begin INT NOT NULL DEFAULT (0),
		shift_sign_end INT NULL,
		shift_late INT NULL,
		shift_early INT NULL,
		shift_duty_on_delay INT NULL,
		shift_duty_off_delay INT NULL,
		shift_workday INT NULL,
		shift_workday_hours INT NULL,
		is_enjoyment_subsidized INT NULL,
		middle_shift_start_time INT NULL,
		middle_shift_end_time INT NULL,
		night_shift_start_time INT NULL,
		night_shift_end_time INT NULL,
		repast_start_time INT NULL,
		repast_end_time INT NULL,
		repast_start_time2 INT NULL,
		repast_end_time2 INT NULL,
		shift_is_rest INT NULL,
		is_have_ot int null,
		is_created_ot int null
	)

	CREATE TABLE #kt_paiba (
		user_serial varchar(200) COLLATE Chinese_PRC_CI_AS,
		rq nvarchar(10) COLLATE Chinese_PRC_CI_AS,
		d1 varchar(100) COLLATE Chinese_PRC_CI_AS,
		d2 varchar(100) COLLATE Chinese_PRC_CI_AS,
		d3 varchar(100) COLLATE Chinese_PRC_CI_AS,
		d4 varchar(100) COLLATE Chinese_PRC_CI_AS,
		d5 varchar(100) COLLATE Chinese_PRC_CI_AS,
		d6 varchar(100) COLLATE Chinese_PRC_CI_AS,
		d7 varchar(100) COLLATE Chinese_PRC_CI_AS,
		d8 varchar(100) COLLATE Chinese_PRC_CI_AS,
		d9 varchar(100) COLLATE Chinese_PRC_CI_AS,
		d10 varchar(100) COLLATE Chinese_PRC_CI_AS,
		d11 varchar(100) COLLATE Chinese_PRC_CI_AS,
		d12 varchar(100) COLLATE Chinese_PRC_CI_AS,
		d13 varchar(100) COLLATE Chinese_PRC_CI_AS,
		d14 varchar(100) COLLATE Chinese_PRC_CI_AS,
		d15 varchar(100) COLLATE Chinese_PRC_CI_AS,
		d16 varchar(100) COLLATE Chinese_PRC_CI_AS,
		d17 varchar(100) COLLATE Chinese_PRC_CI_AS,
		d18 varchar(100) COLLATE Chinese_PRC_CI_AS,
		d19 varchar(100) COLLATE Chinese_PRC_CI_AS,
		d20 varchar(100) COLLATE Chinese_PRC_CI_AS,
		d21 varchar(100) COLLATE Chinese_PRC_CI_AS,
		d22 varchar(100) COLLATE Chinese_PRC_CI_AS,
		d23 varchar(100) COLLATE Chinese_PRC_CI_AS,
		d24 varchar(100) COLLATE Chinese_PRC_CI_AS,
		d25 varchar(100) COLLATE Chinese_PRC_CI_AS,
		d26 varchar(100) COLLATE Chinese_PRC_CI_AS,
		d27 varchar(100) COLLATE Chinese_PRC_CI_AS,
		d28 varchar(100) COLLATE Chinese_PRC_CI_AS,
		d29 varchar(100) COLLATE Chinese_PRC_CI_AS,
		d30 varchar(100) COLLATE Chinese_PRC_CI_AS,
		d31 varchar(100) COLLATE Chinese_PRC_CI_AS,
		gly_no nvarchar(10) COLLATE Chinese_PRC_CI_AS,
		year int ,
		month int
	)
	--将数据插入临时明细表
	insert into #scheduleDetail
	select * from openjson(@scheduleDetailJson)
	WITH (
		user_serial bigint ,
		col_name  VARCHAR(30),
		shift_id INT,
		shift_name VARCHAR(30),
		shift_date DATE,
		shift_begin INT,
		shift_end INT,
		shift_sign_begin INT,
		shift_sign_end INT,
		shift_late INT,
		shift_early INT ,
		shift_duty_on_delay INT,
		shift_duty_off_delay INT,
		shift_workday INT,
		shift_workday_hours INT,
		is_enjoyment_subsidized INT,
		middle_shift_start_time INT,
		middle_shift_end_time INT,
		night_shift_start_time INT,
		night_shift_end_time INT,
		repast_start_time INT,
		repast_end_time INT,
		repast_start_time2 INT,
		repast_end_time2 INT,
		shift_is_rest INT,
		is_have_ot int,
		is_created_ot int
	)


DECLARE @shift_date TABLE
        (
        	shift_year INT,
        	shift_month INT
        )

        INSERT INTO @shift_date (shift_year, shift_month)
        SELECT DISTINCT YEAR(shift_date), MONTH(shift_date) FROM #scheduleDetail

        DECLARE @c INT = 0, @total INT = 0, @year INT, @month INT
        SELECT @total = COUNT(1) FROM @shift_date

        WHILE @c < @total
        BEGIN
          SELECT TOP 1 @year = shift_year, @month = shift_month FROM @shift_date

          INSERT INTO #kt_paiba (user_serial, rq, d1, d2, d3, d4, d5, d6, d7, d8, d9, d10, d11, d12, d13, d14, d15, d16, d17, d18, d19, d20, d21, d22, d23, d24, d25, d26, d27, d28, d29, d30, d31, gly_no, year, month)
          SELECT x.user_serial, CAST(@year AS VARCHAR(4)) + '-' +  RIGHT('00'+CONVERT(VARCHAR(50),@month),2), x.d1, x.d2, x.d3, x.d4, x.d5, x.d6, x.d7, x.d8, x.d9, x.d10, x.d11, x.d12, x.d13, x.d14, x.d15, x.d16, x.d17, x.d18, x.d19, x.d20, x.d21, x.d22, x.d23, x.d24, x.d25, x.d26, x.d27, x.d28, x.d29, x.d30, x.d31, @operator, @year, @month
          FROM (
            SELECT p.user_serial,
						isnull([d1],'N') as [d1],
						isnull([d2],'N') as [d2],
						isnull([d3],'N') as [d3],
						isnull([d4],'N') as [d4],
						isnull([d5],'N') as [d5],
						isnull([d6],'N') as [d6],
						isnull([d7],'N') as [d7],
						isnull([d8],'N') as [d8],
						isnull([d9],'N') as [d9],
						isnull([d10],'N') as [d10],
						isnull([d11],'N') as [d11],
						isnull([d12],'N') as [d12],
						isnull([d13],'N') as [d13],
						isnull([d14],'N') as [d14],
						isnull([d15],'N') as [d15],
						isnull([d16],'N') as [d16],
						isnull([d17],'N') as [d17],
						isnull([d18],'N') as [d18],
						isnull([d19],'N') as [d19],
						isnull([d20],'N') as [d20],
						isnull([d21],'N') as [d21],
						isnull([d22],'N') as [d22],
						isnull([d23],'N') as [d23],
						isnull([d24],'N') as [d24],
						isnull([d25],'N') as [d25],
						isnull([d26],'N') as [d26],
						isnull([d27],'N') as [d27],
						isnull([d28],'N') as [d28],
						isnull([d29],'N') as [d29],
						isnull([d30],'N') as [d30],
						isnull([d31],'N') as [d31]
            FROM (
              SELECT user_serial, shift_name, col_name
              FROM #scheduleDetail
              WHERE YEAR(shift_date) = @year AND MONTH(shift_date) = @month
            ) t
            PIVOT (
            	MAX(shift_name)
            	FOR col_name IN ([d1], [d2], [d3], [d4], [d5], [d6], [d7], [d8], [d9], [d10], [d11], [d12], [d13], [d14], [d15], [d16],
                [d17], [d18], [d19], [d20], [d21], [d22], [d23], [d24], [d25], [d26], [d27], [d28], [d29], [d30], [d31])
            ) p
          ) x

          DELETE FROM @shift_date WHERE shift_year = @year AND shift_month = @month
          SELECT @c += 1
        END


        MERGE
        INTO kt_paiba p
        USING #kt_paiba s ON p.user_serial = s.user_serial
          AND p.rq = s.rq
        WHEN MATCHED
          THEN UPDATE SET
              p.d1 = case when s.d1 = 'N' then p.d1 else s.d1 end
              , p.d2 =case when s.d2 = 'N' then p.d2 else s.d2 end
              , p.d3 =case when s.d3 = 'N' then p.d3 else s.d3 end
              , p.d4 =case when s.d4 = 'N' then p.d4 else s.d4 end
              , p.d5 =case when s.d5 = 'N' then p.d5 else s.d5 end
              , p.d6 =case when s.d6 = 'N' then p.d6 else s.d6 end
              , p.d7 =case when s.d7 = 'N' then p.d7 else s.d7 end
              , p.d8 =case when s.d8 = 'N' then p.d8 else s.d8 end
              , p.d9 =case when s.d9 = 'N' then p.d9 else s.d9 end
              , p.d10 =case when s.d10 = 'N' then p.d10 else s.d10 end
              , p.d11 =case when s.d11 = 'N' then p.d12 else s.d11 end
              , p.d12 =case when s.d12 = 'N' then p.d12 else s.d12 end
              , p.d13 =case when s.d13 = 'N' then p.d13 else s.d13 end
              , p.d14 =case when s.d14 = 'N' then p.d14 else s.d14 end
              , p.d15 =case when s.d15 = 'N' then p.d15 else s.d15 end
              , p.d16 =case when s.d16 = 'N' then p.d16 else s.d16 end
							, p.d17 =case when s.d17 = 'N' then p.d17 else s.d17 end
              , p.d18 =case when s.d18 = 'N' then p.d18 else s.d18 end
              , p.d19 =case when s.d19 = 'N' then p.d19 else s.d19 end
              , p.d20 =case when s.d20 = 'N' then p.d20 else s.d20 end
              , p.d21 =case when s.d21 = 'N' then p.d21 else s.d21 end
              , p.d22 =case when s.d22 = 'N' then p.d22 else s.d22 end
              , p.d23 =case when s.d23 = 'N' then p.d23 else s.d23 end
              , p.d24 =case when s.d24 = 'N' then p.d24 else s.d24 end
              , p.d25 =case when s.d25 = 'N' then p.d25 else s.d25 end
              , p.d26 =case when s.d26 = 'N' then p.d26 else s.d26 end
              , p.d27 =case when s.d27 = 'N' then p.d27 else s.d27 end
              , p.d28 =case when s.d28 = 'N' then p.d28 else s.d28 end
              , p.d29 =case when s.d29 = 'N' then p.d29 else s.d29 end
              , p.d30 =case when s.d30 = 'N' then p.d30 else s.d30 end
              , p.d31 =case when s.d31 = 'N' then p.d31 else s.d31 end
              , p.gly_no = s.gly_no
              , p.year = s.year
              , p.month = s.month
        WHEN NOT MATCHED BY TARGET
          THEN INSERT(user_serial, rq, d1, d2, d3, d4, d5, d6, d7, d8, d9, d10, d11, d12, d13, d14, d15, d16, d17, d18, d19, d20, d21, d22, d23, d24, d25, d26, d27, d28, d29, d30, d31, gly_no, year, month)
              VALUES(user_serial, rq,
							case when d1='N' then '' else d1 end,
							case when d2='N' then '' else d2 end,
							case when d3='N' then '' else d3 end,
							case when d4='N' then '' else d4 end,
							case when d5='N' then '' else d5 end,
							case when d6='N' then '' else d6 end,
							case when d7='N' then '' else d7 end,
							case when d8='N' then '' else d8 end,
							case when d9='N' then '' else d9 end,
							case when d10='N' then '' else d10 end,
							case when d11='N' then '' else d11 end,
							case when d12='N' then '' else d12 end,
							case when d13='N' then '' else d13 end,
							case when d14='N' then '' else d14 end,
							case when d15='N' then '' else d15 end,
							case when d16='N' then '' else d16 end,
							case when d17='N' then '' else d17 end,
							case when d18='N' then '' else d18 end,
							case when d19='N' then '' else d19 end,
							case when d20='N' then '' else d20 end,
							case when d21='N' then '' else d21 end,
							case when d22='N' then '' else d22 end,
							case when d23='N' then '' else d23 end,
							case when d24='N' then '' else d24 end,
							case when d25='N' then '' else d25 end,
							case when d26='N' then '' else d26 end,
							case when d27='N' then '' else d27 end,
							case when d28='N' then '' else d28 end,
							case when d29='N' then '' else d29 end,
							case when d30='N' then '' else d30 end,
							case when d31='N' then '' else d31 end,
							gly_no, year, month)
        ;


				-- 删除数据详情表
				delete a
				from bd_kq_schedule_detail a
				inner join #result b on a.user_serial = b.user_serial and a.shift_date = b.date


				-- 插入数据详情表
				insert into bd_kq_schedule_detail(user_serial,shift_id,shift_name,shift_date,shift_begin,shift_end,shift_sign_begin,shift_sign_end,shift_late,shift_early,shift_duty_on_delay,shift_duty_off_delay,shift_workday,shift_workday_hours,is_enjoyment_subsidized,middle_shift_start_time,middle_shift_end_time,night_shift_start_time,night_shift_end_time,repast_start_time,repast_end_time,repast_start_time2,repast_end_time2,shift_is_rest,is_have_ot,is_created_ot,update_id,update_time)
				SELECT user_serial, shift_id, shift_name, shift_date, shift_begin, shift_end, shift_sign_begin, shift_sign_end, shift_late, shift_early, shift_duty_on_delay, shift_duty_off_delay, shift_workday, shift_workday_hours, is_enjoyment_subsidized, middle_shift_start_time, middle_shift_end_time, night_shift_start_time, night_shift_end_time, repast_start_time, repast_end_time, repast_start_time2, repast_end_time2, shift_is_rest,is_have_ot,is_created_ot,@operator,getdate()
        FROM #scheduleDetail
				where shift_id != 0


				--检测放班
				--创建一个游标
				declare my_cursor cursor for select user_serial,shift_date from #scheduleDetail

				--变量
				declare @date date;
				declare @user_serial varchar(200);
				--打开游标
				open my_cursor
				fetch next from my_cursor into @user_serial,@date  --获取my_cursor的下一条数据，其中为两个字段分别赋值给@id,@name
				while @@FETCH_STATUS=0 begin --假如检索到了数据继续执行
					--1调用放班操作请假单存储过程
					--参数：@user_serial  ，@date
					EXEC bd_proc_schedule_fb_leave @user_serial,@date,@operator

					fetch next from my_cursor into @user_serial,@date --获取下一条数据并赋值给变量
				end--关闭释放游标
				close my_cursor
				deallocate my_cursor


				--考勤重新计算
				DECLARE @str varchar(max)= '';

				SELECT @str = @str + CAST(u.user_serial AS VARCHAR(100)) + ',' FROM (
					select DISTINCT user_serial from #scheduleDetail
				) u

				SET @str = SUBSTRING(@str,0, LEN(@str))

				-- 调用考勤计算(排班4)
				if @doType = 0
				BEGIN
					EXEC bd_proc_timecard_extend @begin_date,@end_date,@str,4
				END

END
go
